use master

create database InfoPackage
on
(
	Name = 'InfoPackage',
	FileName = 'E:\InfoPackage.mdf',
	Size = 10MB,
	MaxSize = 80Mb,
	FileGrowth = 10%
)

log on
(
	Name = 'InfoPackage_log',
	FileName = 'E:\InfoPackage_log.ldf',
	Size = 10Mb,
	MaxSize = 50Mb,
	FileGrowth = 10%
)
go

use InfoPackage
go

Create table Class
(
	ClassId int primary key identity(1,1),
	ClassName nvarchar(10) unique not null
)
go

insert into Class (ClassId, ClassName) value (1, '一班'),
	(2, '二班'),(3, '三班'),(4, '四班'),(5, '五班'),
	(6, '六班'),(7, '七班'),(8, '八班'),(9, '九班'),
	(10, '十班')
update Class set ClassName = '十一班' where ClassId = 1
delete from Class where ClassId = 10
go

Create table Student
(
	StuId int primary key identity(1, 1),
	ClassId int foreign key references Class(ClassId),
	StuName nvarchar not null,
	StuSex nchar(1) default '男' Check (StuSex in ('男', '女'))
	StuBirth date,
	StuPhone nvarchar(11) unique,
	StuAddress Text,
)

insert into Student (StuId, ClassId, StuName, StuSex, SruBirthday,
	StuPhone,StudentAddress)
	value (1, 11, '张?', '男', '1990-1-?', '1234567890?', '?1'),
	value (2, 11, '张?', '男', '1990-1-?', '1234567890?', '?1'),
	value (3, 2, '张?', '男', '1990-1-?', '1234567890?', '?1'),
	value (4, 2, '张?', '男', '1990-1-?', '1234567890?', '?1'),
	value (5, 3, '张?', '男', '1990-1-?', '1234567890?', '?1'),
	value (6, 3, '张?', '男', '1990-1-?', '1234567890?', '?1'),
	value (7, 4, '张?', '男', '1990-1-?', '1234567890?', '?1'),
	value (8, 4, '张?', '男', '1990-1-?', '1234567890?', '?1'),
	value (9, 5, '张?', '男', '1990-1-?', '1234567890?', '?1'),
	value (10, 5, '张?', '男', '1990-1-?', '1234567890?', '?1'),
	value (11, 6, '张?', '男', '1990-1-?', '1234567890?', '?1'),
	value (12, 6, '张?', '男', '1990-1-?', '1234567890?', '?1'),
	value (13, 7, '张?', '男', '1990-1-?', '1234567890?', '?1'),
	value (14, 7, '张?', '男', '1990-1-?', '1234567890?', '?1'),
	value (15, 8, '张?', '男', '1990-1-?', '1234567890?', '?1'),
	value (16, 8, '张?', '男', '1990-1-?', '1234567890?', '?1'),
	value (17, 9, '张?', '男', '1990-1-?', '1234567890?', '?1'),
	value (18, 9, '张?', '男', '1990-1-?', '1234567890?', '?1'),
	value (19, 10, '张?', '男', '1990-1-?', '1234567890?', '?1'),
	value (20, 10, '张?', '男', '1990-1-?', '1234567890?', '?1')

Alter table Student 
Add CreateDate datetime default (getdate())

update Student set CreateDate = '2020-1-1 0:0:0' where StuId <> 0
delete from Student where ClassId = 5
go

Create table Course
(
	CourseId int primary identity(1, 1),
	CourseName nvarchar(10) unique not null,
	CourseCredit int not null default 1 check(CourseCredit <= 5 and CourseCredit >= 1),
	CourseCredit nvarchar(10) check(CourseCredit in ('专业课', '公共课'))
)
go

insert into Course(CourseName,CourseCredit) values('英语',4),('语文',4),('数学',4),('数据库',5),('体育',4),('计算机应用',5)
select * from Course
update Course set CourseCredit=3 where CourseName='英语'
go

create table Score
(
ScoreId int primary key identity(1,1),
Stuid int foreign key references Students(Stuid),
CourseId int foreign key references Course(Courseid),
Score decimal(5,2) unique not null
)
insert into Score(Stuid,CourseId,Score) values
(61,3,96),(62,3,97),(63,3,94),(71,3,66),(68,3,56),(69,3,46),(72,3,99),(74,3,26),(76,3,76),(78,3,90),
(64,3,91.6),(65,3,90.6),(66,3,97.5),(67,3,96.5),(70,3,91.2),(73,3,65),(75,3,79),(77,3,77),(79,3,88),(80,3,86)
delete from Score where courseId=1
alter table Score add  constraint CK_Score_Score  check(Score >= 0 and Score<=100 ) 
alter table Score add  constraint DK_Score_Score default(0) for Score 
go


